﻿Imports MySql.Data

Public Class MySQL
    Public Shared conn As New MySqlClient.MySqlConnection("Server=" & Config.MySQLServer & _
                                                          ";Database=" & Config.MySQLDatabase & _
                                                          ";Uid=" & Config.MySQLUsername & ";Pwd=" _
                                                          & Config.MySQLPassword & ";")
    Public Shared command As MySqlClient.MySqlCommand
    Public Shared dataReader As MySqlClient.MySqlDataReader
    Public Shared trans As MySqlClient.MySqlTransaction

    Public Shared Sub setUp()
        log.log(LogType.MSG_INFO)
        Console.Write("Loading MySQL...")
        conn.Open()
        conn.Close()
        Console.WriteLine("DONE")
    End Sub

    Public Shared Function testLogin(ByVal username As String, ByVal password As String, ByRef userID As Integer) As Boolean
        Try
            command = New MySqlClient.MySqlCommand("select * from accounts where username = '" & username & "' and password = '" & password & "'", conn)
            conn.Open()
            trans = conn.BeginTransaction()
            trans.Commit()
            dataReader = command.ExecuteReader()
            Do While (dataReader.Read())
                userID = dataReader.GetInt32("userid")
                conn.Close()
                Return True
            Loop
            conn.Close()
            Return False
        Catch ex As MySqlClient.MySqlException
            conn.Close()
            Return False
        End Try
    End Function

    Public Shared Sub setOnline(ByVal userid As Integer, Optional ByVal val As Integer = 1)
        Try
            command = New MySqlClient.MySqlCommand("update accounts set online = '" & val & "' where userid = '" & userid & "'", conn)
            conn.Open()
            trans = conn.BeginTransaction()
            trans.Commit()
            command.ExecuteNonQuery()
            conn.Close()
        Catch ex As MySqlClient.MySqlException
        End Try
    End Sub

    Public Shared Function getCharacters(ByVal userID As Integer) As List(Of Character)
        Dim charList As New List(Of Character)
        Try
            command = New MySqlClient.MySqlCommand("select * from characters where accountid = '" & userID & "' and deleted = '0'", conn)
            conn.Open()
            trans = conn.BeginTransaction()
            trans.Commit()
            dataReader = command.ExecuteReader()
            Do While (dataReader.Read())
                Dim c As New Character
                c.slot = dataReader.GetInt32("slot")
                c.mapID = dataReader.GetInt32("map_id")
                c.name = dataReader.GetString("name")
                c.x = dataReader.GetFloat("x")
                c.y = dataReader.GetFloat("y")
                c.z = dataReader.GetFloat("z")
                c.id = dataReader.GetInt32("charid")
                c.hairstyle = dataReader.GetInt32("hair_style")
                c.haircolor = dataReader.GetInt32("hair_color")
                c.face = dataReader.GetInt32("face")
                c.gender = dataReader.GetInt32("gender")
                c.job = dataReader.GetInt32("job")
                c.level = dataReader.GetInt32("level")
                c.str = dataReader.GetInt32("str")
                c.sta = dataReader.GetInt32("sta")
                c.dex = dataReader.GetInt32("dex")
                c.intt = dataReader.GetInt32("intt")
                charList.Add(c)
            Loop
            conn.Close()
            Return charList
        Catch ex As MySqlClient.MySqlException
            conn.Close()
            Return charList
        End Try
    End Function

    Public Shared Function getEquips(ByVal charID As Integer, ByRef array As List(Of Integer)) As Integer
        Dim ret As Integer = 0
        Try
            command = New MySqlClient.MySqlCommand("select itemid from equips where charid = '" & charID & "'", conn)
            conn.Open()
            trans = conn.BeginTransaction()
            trans.Commit()
            dataReader = command.ExecuteReader()
            Dim itemID As Integer = Nothing
            Do While (dataReader.Read())
                itemID = dataReader.GetInt32("itemid")
                array.Add(itemID)
                ret += 1
            Loop
            conn.Close()
            Return ret
        Catch ex As MySqlClient.MySqlException
            conn.Close()
            Return ret
        End Try
    End Function

    Public Shared Function createChar(ByVal accountID As Integer, ByVal name As String, ByVal slot As Byte, _
                                 ByVal hair As Byte, ByVal haircolor As Integer, ByVal gender As Byte, _
                                 ByVal face As Byte) As Boolean
        Try
            command = New MySqlClient.MySqlCommand("select * from characters where name = ?name", conn)
            command.Parameters.AddWithValue("?name", name)

            conn.Open()
            trans = conn.BeginTransaction()
            trans.Commit()

            dataReader = command.ExecuteReader()
            Do While dataReader.Read()
                conn.Close()
                Return False
            Loop
            dataReader.Close()

            command = New MySqlClient.MySqlCommand("insert into characters(name, accountid, slot, hair_style, " & _
                                                   "hair_color, gender, face) values(?name, ?accountid, ?slot, " & _
                                                   "?hair, ?haircolor, ?gender, ?face)", conn)
            command.Parameters.AddWithValue("?name", name)
            command.Parameters.AddWithValue("?accountid", accountID)
            command.Parameters.AddWithValue("?slot", slot)
            command.Parameters.AddWithValue("?hair", hair)
            command.Parameters.AddWithValue("?haircolor", haircolor)
            command.Parameters.AddWithValue("?gender", gender)
            command.Parameters.AddWithValue("?face", face)

            command.ExecuteNonQuery()
            conn.Close()
            Return True
        Catch ex As MySqlClient.MySqlException
            conn.Close()
            Return False
        End Try
    End Function

    Public Shared Sub deleteChar(ByVal charID As Integer)
        Try
            command = New MySqlClient.MySqlCommand("update characters set deleted = '1' where charid = '" & charID & "'", conn)
            conn.Open()
            trans = conn.BeginTransaction()
            trans.Commit()
            command.ExecuteNonQuery()
            conn.Close()
        Catch ex As MySqlClient.MySqlException
            conn.Close()
        End Try
    End Sub

    Public Shared Sub createEquip(ByVal charid As Integer, ByVal itemid As Integer)
        Try
            command = New MySqlClient.MySqlCommand("insert into equips(charid, itemid) values('" & charid & "', '" & itemid & "')", conn)
            conn.Open()
            trans = conn.BeginTransaction()
            trans.Commit()
            command.ExecuteNonQuery()
            conn.Close()
        Catch ex As MySqlClient.MySqlException
            conn.Close()
        End Try
    End Sub

    Public Shared Function getCharID(ByVal name As String) As Integer
        Try
            command = New MySqlClient.MySqlCommand("select charid from characters where name = ?name", conn)
            command.Parameters.AddWithValue("?name", name)
            conn.Open()
            trans = conn.BeginTransaction()
            trans.Commit()

            dataReader = command.ExecuteReader()
            Dim ret As Integer = -1
            Do While dataReader.Read()
                ret = dataReader.GetInt32("charid")
            Loop

            conn.Close()
            Return ret
        Catch ex As MySqlClient.MySqlException
            conn.Close()
            Return -1
        End Try
    End Function

End Class
